Excel Formulas Cheat Sheet - WittyWriter

Excel Formulas Cheat Sheet

📘 Key Concepts and Definitions

Formula: An expression that calculates the value of a cell. All formulas in Excel must begin with an equals sign (=).
Function: A predefined formula that performs a specific calculation (e.g., SUM, AVERAGE).
Cell: A single box in the worksheet, identified by its column letter and row number (e.g., A1).
Range: A group of two or more cells. Specified by the top-left cell and bottom-right cell, separated by a colon (e.g., A1:C5).
Relative Reference: A standard cell reference (e.g., A1) that changes automatically when the formula is copied to another cell.
Absolute Reference: A cell reference that remains fixed when a formula is copied. Indicated by a dollar sign ($) before the column letter, row number, or both (e.g., $A$1).

The basic syntax for a function is its name followed by one or more arguments in parentheses.

=FUNCTION_NAME(argument1, argument2, ...)

FUNCTION_NAME: The name of the function (e.g., VLOOKUP).
argument1: The first required input for the function, which can be a value, cell reference, range, or another function.
argument2: Optional or required additional inputs.

Example: =SUM(A1:A10) calculates the sum of the values in the range A1 through A10.

Function	Syntax	Description
SUM	`=SUM(range)`	Adds all numbers in a range of cells.
AVERAGE	`=AVERAGE(range)`	Calculates the average of numbers in a range.
COUNT	`=COUNT(range)`	Counts the number of cells that contain numbers.
COUNTA	`=COUNTA(range)`	Counts the number of cells that are not empty.
MAX / MIN	`=MAX(range)`	Returns the largest / smallest value in a range.

Function	Syntax	Description
IF	`=IF(logical_test, value_if_true, value_if_false)`	Checks if a condition is met and returns one value if true, and another if false.
AND / OR	`=AND(logical1, [logical2], ...)`	Returns TRUE if all arguments are true (AND) or if any argument is true (OR). Often used inside an IF statement.
IFERROR	`=IFERROR(value, value_if_error)`	Returns a specified value if a formula evaluates to an error; otherwise, returns the result of the formula.

Function	Syntax	Description
VLOOKUP	`=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])`	Looks for a value in the first column of a table and returns a value in the same row from a specified column.
INDEX	`=INDEX(array, row_num, [col_num])`	Returns a value from a table or range based on a row and column number.
MATCH	`=MATCH(lookup_value, lookup_array, [match_type])`	Finds the position of a value within a range.
XLOOKUP	`=XLOOKUP(lookup_value, lookup_array, return_array, ...)`	A modern, more flexible replacement for VLOOKUP. (Available in newer Excel versions).

Combining INDEX and MATCH is more powerful and flexible than VLOOKUP because it can look up values in any column, not just the first one.

Goal: Find a value in a table based on a criterion.
MATCH: First, use MATCH to find the row number of your lookup value. =MATCH("Product B", A1:A10, 0) might return `3`, meaning "Product B" is in the 3rd row.
INDEX: Then, use INDEX to retrieve the value from the desired column at that row number. =INDEX(B1:B10, [result from MATCH]).
Combine: Nest the MATCH function inside the INDEX function.
```
=INDEX(B1:B10, MATCH("Product B", A1:A10, 0))
```
This formula finds "Product B" in column A and returns the corresponding value from column B.

Toggle References (F4): After entering a cell reference in a formula, press F4 to cycle between relative (A1), absolute ($A$1), mixed row (A$1), and mixed column ($A1).
Show All Formulas (Ctrl + `): Press Ctrl and the backtick key (`) to display all formulas in the worksheet instead of their results. Press it again to switch back.
AutoSum (Alt + =): Select a cell below a column of numbers (or to the right of a row) and press Alt + = to automatically insert a SUM formula.
Flash Fill (Ctrl + E): If you have a pattern in your data (like extracting first names from a full name column), type one example, then go to the cell below and press Ctrl + E. Excel will attempt to fill in the rest automatically.

Feature	VLOOKUP	XLOOKUP
Lookup Column	Must be the first column in the table array.	Can be any column.
Return Column	Specified by a column number, which can break if columns are inserted/deleted.	Specified as a separate range, which is more robust.
Default Match	Approximate match by default (often causes errors).	Exact match by default (safer).
Direction	Can only look from left to right.	Can look in any direction. Can also search from bottom-to-top.

Goal: Calculate a 10% commission for salespeople who sold over $5,000, and 5% for all others. The sales amount is in cell B2.

=IF(B2 > 5000, B2 * 0.10, B2 * 0.05)

#VALUE!: An incorrect data type is used. For example, trying to add a number to a text string.
#REF!: The formula refers to a cell that is not valid. This often happens when you delete a cell, row, or column that a formula was referencing.
#N/A: "Not Available." The formula cannot find the value it was asked to look for. Common in VLOOKUP or MATCH.
#DIV/0!: You are trying to divide a number by zero. Check your denominator.
#NAME?: Excel doesn't recognize the text in the formula, usually because of a typo in a function name.
Circular Reference Warning: A formula refers back to its own cell, creating an infinite loop. Excel will usually point you to the cell causing the issue.